using ProjectManager.Interface;
using ProjectManager.Logic.Content;
using ProjectManager.UnderLogic;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;

namespace ProjectManager.Controller
{
    internal class LocalFileController : ILocalFile
    {
        private readonly string rootPath;
        private readonly string ProjectsDirectory;
        private readonly string DBPath;
        private const string ProjectInfoTableName = "ProjectInfo";
        private const string UserConfigTableName = "UserConfig";

        private static SQLiteConnection? conn;
        public LocalFileController()
        {
            rootPath = Application.StartupPath;
            ProjectsDirectory = Path.Combine(rootPath, "Projects");
            DBPath = Path.Combine(rootPath, "DataBase");

            Directory.CreateDirectory(ProjectsDirectory);
            Directory.CreateDirectory(DBPath);

            DBPath = Path.Combine(DBPath, "ProjectInfoDB.db");

            if (conn == null)
            {
                conn = new($"Data Source={DBPath};Version=3;");
                conn.Open();
            }
        }
        ~LocalFileController()
        {
            conn?.Close();
        }

        public void GenerateProjectPath(string Url, out string rootPath, out string taskPath, out string memberPath)
        {
            string rtP = Url.Split("/").Last();
            if (rtP.Contains("."))
                rtP = rtP.Split(".").First();
            rootPath = Path.Combine(ProjectsDirectory, rtP);
            taskPath = Path.Combine(rootPath, "Tasks");
            memberPath = Path.Combine(rootPath, "Members");

            Directory.CreateDirectory(rootPath);
            Directory.CreateDirectory(taskPath);
            Directory.CreateDirectory(memberPath);
        }

        public string GetRootPath()
        {
            return ProjectsDirectory;
        }

        void ILocalFile.UpdateProject(ProjectInformation projectInformation, MemberInformation memberInformation
            , string userName, string password)
        {
            // 查看表是否存在，若不存在则创建
            string createTableQuery = $"""
                                        CREATE TABLE IF NOT EXISTS {ProjectInfoTableName} (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增
                        Url TEXT NOT NULL,                    -- URL，不可为空
                        Path TEXT NOT NULL,                   -- 路径，不可为空
                        Name TEXT NOT NULL,                   -- 名称，不可为空
                        TaskPath TEXT,                        -- 任务路径，可为空
                        MemberPath TEXT,                      -- 成员路径，可为空
                        MemberID INTEGER,                     -- 成员ID，不可为空
                        MemberName TEXT,                      -- 成员名称，可为空
                        UserName TEXT,                        -- 用户名，不可为空
                        Password TEXT                         -- 密码，不可为空
                    );
                    """;
            SQLiteCommand command = new(createTableQuery, conn);
            command.ExecuteNonQuery();

            // 检查条目是否存在，若存在则更新，若不存在则插入
            string updateSql = string.Empty;
            string checkEntryQuery = $"""
                                        SELECT *
                                        FROM {ProjectInfoTableName} WHERE Url = "{projectInformation.Url}";
                                        """;
            command = new SQLiteCommand(checkEntryQuery, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            if (reader.Read())
                updateSql = $"""
                        update {ProjectInfoTableName}
                        set {projectInformation.ToSqlKeyString()}, {memberInformation.ToSqlKeyString()}
                        where Url = "{projectInformation.Url}";
                        """;
            else
                updateSql = $"""
                        insert into {ProjectInfoTableName}
                        (Url, Path, Name, TaskPath, MemberPath, MemberID, MemberName, UserName, Password)
                        values
                        ({projectInformation.ToSqlString()}, {memberInformation.ToSqlString()}, "{userName}", "{password}");
                        """;
            command = new SQLiteCommand(updateSql, conn);
            command.ExecuteNonQuery();

        }
        public static (string, string) GetUsernamePassword(string url)
        {
            // 查询所有项目信息
            string querySql = $"""
                                SELECT *
                                FROM {ProjectInfoTableName}
                                WHERE Url = "{url}";
                                """;
            SQLiteCommand command = new(querySql, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                return (reader.GetString(8), reader.GetString(9));
            }
            else
            {
                throw new Exception("未找到项目信息");
            }
        }

        void ILocalFile.UpdateConfig(UserConfig userConfig)
        {
            // 查看表是否存在，若不存在则创建
            string createTableQuery = $"""
                                        CREATE TABLE IF NOT EXISTS {UserConfigTableName} (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增
                        Url TEXT NOT NULL,                    -- URL，不可为空
                        Path TEXT NOT NULL,                   -- 路径，不可为空
                        Name TEXT NOT NULL,                   -- 名称，不可为空
                        TaskPath TEXT,                        -- 任务路径，可为空
                        MemberPath TEXT,                      -- 成员路径，可为空
                        MemberID INTEGER,                     -- 成员ID，不可为空
                        MemberName TEXT                       -- 成员名称，可为空
                    );
                    """;
            SQLiteCommand command = new(createTableQuery, conn);
            command.ExecuteNonQuery();

            // 插入新条目
            string insertSql = $$"""
                                insert into {{UserConfigTableName}}
                                (Url, Path, Name, TaskPath, MemberPath, MemberID, MemberName)
                                values
                                ({{userConfig.CurrentProject.ToSqlString()}}, {{userConfig.CurrentIdenity.ToSqlString()}});
                                """;
            command = new SQLiteCommand(insertSql, conn);
            command.ExecuteNonQuery();
        }

        UserConfig? ILocalFile.GetUserConfig()
        {
            UserConfig ? result = null;
            // 查看表是否存在，若不存在则创建
            string createTableQuery = $"""
                                        CREATE TABLE IF NOT EXISTS {UserConfigTableName} (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增
                        Url TEXT NOT NULL,                    -- URL，不可为空
                        Path TEXT NOT NULL,                   -- 路径，不可为空
                        Name TEXT NOT NULL,                   -- 名称，不可为空
                        TaskPath TEXT,                        -- 任务路径，可为空
                        MemberPath TEXT,                      -- 成员路径，可为空
                        MemberID INTEGER,                     -- 成员ID，不可为空
                        MemberName TEXT                       -- 成员名称，可为空
                    );
                    """;
            SQLiteCommand command = new(createTableQuery, conn);
            command.ExecuteNonQuery();

            // 查询所有项目信息
            string querySql = $"""
                                SELECT *
                                FROM {UserConfigTableName};
                                """;
            command = new(querySql, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ProjectInformation projectInformation = new()
                {
                    Url = reader.GetString(1),
                    Path = reader.GetString(2),
                    Name = reader.GetString(3),
                    TaskPath = reader.GetString(4),
                    MemberPath = reader.GetString(5),
                };
                MemberInformation memberInformation = new()
                {
                    ID = reader.GetInt32(6),
                    Name = reader.GetString(7),
                };
                result = new UserConfig()
                {
                    CurrentProject = projectInformation,
                    CurrentIdenity = memberInformation,
                };
            }
            return result;
        }

        public List<(ProjectInformation, MemberInformation)> GetProjectList()
        {
            List<(ProjectInformation, MemberInformation)> projectList = [];

            // 查看表是否存在，若不存在则创建
            string createTableQuery = $"""
                                        CREATE TABLE IF NOT EXISTS {ProjectInfoTableName} (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增
                        Url TEXT NOT NULL,                    -- URL，不可为空
                        Path TEXT NOT NULL,                   -- 路径，不可为空
                        Name TEXT NOT NULL,                   -- 名称，不可为空
                        TaskPath TEXT,                        -- 任务路径，可为空
                        MemberPath TEXT,                      -- 成员路径，可为空
                        MemberID INTEGER,                     -- 成员ID，不可为空
                        MemberName TEXT,                      -- 成员名称，可为空
                        UserName TEXT,                        -- 用户名，不可为空
                        Password TEXT                         -- 密码，不可为空
                    );
                    """;
            SQLiteCommand command = new(createTableQuery, conn);
            command.ExecuteNonQuery();

            // 查询所有项目信息
            string querySql = $"""
                                SELECT *
                                FROM {ProjectInfoTableName};
                                """;
            command = new(createTableQuery, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ProjectInformation projectInformation = new()
                {
                    Url = reader.GetString(1),
                    Path = reader.GetString(2),
                    Name = reader.GetString(3),
                    TaskPath = reader.GetString(4),
                    MemberPath = reader.GetString(5),
                };
                MemberInformation memberInformation = new()
                {
                    ID = reader.GetInt32(6),
                    Name = reader.GetString(7),
                };
                projectList.Add((projectInformation, memberInformation));
            }
            return projectList;
        }
    }
}